-- @owner: @l3007kkk
-- @date: 2025/08/05
-- @testpoint:ddl语句使用@@rowcount

--连接D库 expect返回0
@conn PrimaryDbDmode;
--创建插件
--create extension shark;
--step1:数据库相关的ddl, 返回0;expect:成功
drop database if exists t_rowcount0010_db;
create database t_rowcount0010_db;
select @@rowcount;
alter database t_rowcount0010_db set datestyle to 'ISO, MDY';
select @@rowcount;
drop database t_rowcount0010_db;
select @@rowcount;

--step2:模式相关的ddl, 返回0;expect:成功
drop schema if exists t_rowcount0010_schema;
create schema t_rowcount0010_schema;
select @@rowcount;
alter schema t_rowcount0010_schema rename to t_rowcount0010_schema1;
select @@rowcount;
drop schema t_rowcount0010_schema1;
select @@rowcount;

--step3:表空间相关的ddl, 返回0;expect:成功
drop tablespace if exists tablespace_rowcount;
create tablespace tablespace_rowcount RELATIVE LOCATION 'row_tablespace/tablespace_1';
select @@rowcount;
alter tablespace tablespace_rowcount rename to new_tablespace_rowcount;
select @@rowcount;
drop tablespace new_tablespace_rowcount;
select @@rowcount;

--step4:存储过程相关的ddl, 返回0;expect:成功
drop procedure if exists pack_rowcount;
create or replace procedure pro_rowcount()
as
begin
 raise notice 'pro_rowcount';
end;
/
select @@rowcount;
drop procedure pro_rowcount;
select @@rowcount;

--step5:函数相关的ddl, 返回0;expect:成功
drop function if exists fun_rowcount;
create or replace function func_0011()
returns int
as
$$
declare
i int;
begin
 i := 0;
 while i < 100 loop
 i := i + 1;
end loop;
return i;
end;
$$
language pltsql;
select @@rowcount;

--step6:调用函数, 返回1;expect:成功
call func_0011();
select @@rowcount;

--step7:删除函数, 返回0;expect:成功
drop function func_0011;
select @@rowcount;
--step8:创建源表及触发表
drop table if exists t_rowcount0010_src_tbl;
drop table if exists t_rowcount0010_des_tbl;
CREATE TABLE t_rowcount0010_src_tbl(id1 INT, id2 INT, id3 INT);
CREATE TABLE t_rowcount0010_des_tbl(id1 INT, id2 INT, id3 INT);

--step9:创建触发器函数后查询，返回0;expect:成功
CREATE OR REPLACE FUNCTION tri_insert_func() RETURNS TRIGGER AS
$$
DECLARE
BEGIN
INSERT INTO t_rowcount0010_des_tbl VALUES(NEW.id1, NEW.id2, NEW.id3);
RETURN NEW;
END
$$
LANGUAGE PLPGSQL;
select @@rowcount;

--step10:创建insert触发后查询，返回0;expect:成功
drop TRIGGER if exists t_rowcount0010_insert_trigger;
CREATE TRIGGER t_rowcount0010_insert_trigger
    BEFORE INSERT ON t_rowcount0010_src_tbl
    FOR EACH ROW
    EXECUTE PROCEDURE tri_insert_func();
select @@rowcount;

--step11:删除触发器使用函数，返回0;expect:成功
DROP TRIGGER t_rowcount0010_insert_trigger ON t_rowcount0010_src_tbl;
select @@rowcount;

--step12:视图相关的ddl，返回0;expect:成功
drop view if exists view_rowcount;
create or replace view view_rowcount as select * from pg_tables;
select @@rowcount;
drop view view_rowcount;
select @@rowcount;

--step13:插件相关的ddl，返回0;expect:成功
drop extension if exists t_rowcount0010_gms_utility;
create extension t_rowcount0010_gms_utility;
select @@rowcount;
drop extension t_rowcount0010_gms_utility;
select @@rowcount;

--step14:数据类型相关的ddl，返回0;expect:成功
drop type if exists t_rowcount0010_type_rowcount;
create type t_rowcount0010_type_rowcount as (col1 int,col2 int);
select @@rowcount;
drop type t_rowcount0010_type_rowcount;
select @@rowcount;

--step15:创建序列，返回0;expect:成功
drop sequence if exists t_rowcount0010_seq_rowcount;
create sequence t_rowcount0010_seq_rowcount;
select @@rowcount;

--step16:查询序列值后使用函数，返回1;expect:成功
select nextval('t_rowcount0010_seq_rowcount');
select @@rowcount;

--step17:-删除序列，返回0;expect:成功
drop sequence t_rowcount0010_seq_rowcount;
select @@rowcount;

--step18:同义词相关的ddl，返回0;expect:成功
drop synonym if exists t_rowcount0010_syn_rowcount;
create synonym t_rowcount0010_syn_rowcount for pg_tables;
select @@rowcount;
drop synonym t_rowcount0010_syn_rowcount;
select @@rowcount;

--step19:清理环境;expect:成功
drop database if exists t_rowcount0010_db;
drop table if exists t_rowcount0010_src_tbl;
drop table if exists t_rowcount0010_des_tbl;